package com.define.ssh.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ImportExcel_xjwy {

	private static String EXCEL_2003 = ".xls";
	private static String EXCEL_2007 = ".xlsx";

	public static void readExcelJXL() {
	}

	/**
	 * 通过POI方式读取Excel
	 * 
	 * @param excelFile
	 */
	public static DataSet readExcelPOI(String filePath, Integer cons)
			throws Exception {
		final File excelFile = new File(filePath);
		if (excelFile != null) {
			String fileName = excelFile.getName();
			fileName = fileName.toLowerCase();
			if (fileName.toLowerCase().endsWith(EXCEL_2003)) {
				final DataSet dataSet = readExcelPOI2003(excelFile, cons);
				return dataSet;
			}
			if (fileName.toLowerCase().endsWith(EXCEL_2007)) {
				final DataSet dataSet = readExcelPOI2007(excelFile, cons);
				return dataSet;
			}
		}
		return null;
	}

	/**
	 * 通过POI方式读取Excel
	 * 
	 * @param excelFile
	 */
	public static DataSet readExcelPOI(File file, Integer cons, String fileName)
			throws Exception {
		final File excelFile = file;
		if (excelFile != null) {
			fileName = fileName.toLowerCase();
			if (fileName.toLowerCase().endsWith(EXCEL_2003)) {
				final DataSet dataSet = readExcelPOI2003(excelFile, cons);
				return dataSet;
			}
			if (fileName.toLowerCase().endsWith(EXCEL_2007)) {
				final DataSet dataSet = readExcelPOI2007(excelFile, cons);
				return dataSet;
			}
		}
		return null;
	}

	/**
	 * 读取Excel2003的表单
	 * 
	 * @param excelFile
	 * @return
	 * @throws Exception
	 */
	private static DataSet readExcelPOI2003(File excelFile, Integer rCons)
			throws Exception {
		final List<String[]> datasList = new ArrayList<String[]>();
		final Set<String> colsSet = new HashSet<String>();
		final InputStream input = new FileInputStream(excelFile);
		final HSSFWorkbook workBook = new HSSFWorkbook(input);
		// 获取Excel的sheet数量
		final Integer sheetNum = workBook.getNumberOfSheets();
		// 循环Sheet表单
		String[] headers = null;
		for (int i = 0; i < sheetNum; i++) {
			final HSSFSheet sheet = workBook.getSheetAt(i);
			if (sheet == null) {
				continue;
			}

			// 获取Sheet里面的Row数量
			final Integer rowNum = sheet.getLastRowNum() + 1;
			for (int j = 0; j < rowNum; j++) {
				// System.out.println("===========");
				final HSSFRow row = sheet.getRow(j);
				if (row == null) {
					continue;
				}
				final Integer cellNum = row.getLastCellNum() + 1;
				// = new String[cellNum+1];
				final String[] datas = new String[cellNum + 1];
				for (int k = 0; k < cellNum; k++) {
					final HSSFCell cell = row.getCell(k);
					if (cell == null) {
						continue;
					}
					if (cell != null) {
						cell.setCellType(Cell.CELL_TYPE_STRING);
						String cellValue = "";
						final int cellValueType = cell.getCellType();
						if (cellValueType == Cell.CELL_TYPE_STRING) {
							cellValue = cell.getStringCellValue();
						}
						if (cellValueType == Cell.CELL_TYPE_NUMERIC) {
							final Double number = cell.getNumericCellValue();
							// System.out.println("字符串+++=========="
							// + number.intValue());
							cellValue = cell.getNumericCellValue() + "";
						}
						if (rCons == k) {
							colsSet.add(cellValue);
						}
						// System.out.println(cellValue);
						datas[k] = cellValue;
					}
				}
				datas[cellNum] = sheet.getSheetName();// 将sheet的名称作为最后一列
				if (j == 0) {
					headers = datas;
				} else {
					datasList.add(datas);
				}

			}
		}
		final DataSet dataSet = new DataSet(headers, null, datasList, colsSet);
		return dataSet;

	}

	/**
	 * 读取Excel2007的表单
	 * 
	 * @param excelFile
	 * @return
	 * @throws Exception
	 */
	private static DataSet readExcelPOI2007(File excelFile, Integer rCons)
			throws Exception {
		final List<String[]> datasList = new ArrayList<String[]>();
		final Set<String> cosSet = new HashSet<String>();
		final InputStream input = new FileInputStream(excelFile);
		final XSSFWorkbook workBook = new XSSFWorkbook(input);
		// 获取Sheet数量
		String[] headers = null;
		final Integer sheetNum = workBook.getNumberOfSheets();
		for (int i = 0; i < sheetNum; i++) {
			final XSSFSheet sheet = workBook.getSheetAt(i);
			if (sheet == null) {
				continue;
			}
			// 获取行值
			final Integer rowNum = sheet.getLastRowNum() + 1;
			System.out.println(i + ":" + rowNum);
			for (int j = 0; j < rowNum; j++) {
				// if (j > rCons) {
				// System.out.println("=============");
				final XSSFRow row = sheet.getRow(j);
				if (row == null) {
					continue;
				}
				final Integer cellNum = row.getLastCellNum() + 1;
				final String[] datas = new String[cellNum + 1];
				for (int k = 0; k < cellNum; k++) {
					final XSSFCell cell = row.getCell(k);
					if (cell == null) {
						continue;
					}
					if (cell != null) {
						cell.setCellType(Cell.CELL_TYPE_STRING);
						String cellValue = "";
						final int cellValueType = cell.getCellType();
						if (cellValueType == Cell.CELL_TYPE_STRING) {
							cellValue = cell.getStringCellValue();
						}
						if (cellValueType == Cell.CELL_TYPE_NUMERIC) {
							final Double number = cell.getNumericCellValue();
							// System.out.println("字符串+++=========="
							// + number.toString());
							cellValue = cell.getNumericCellValue() + "";
						}
						// System.out.println(cellValue);
						if (rCons == k) {
							cosSet.add(cellValue);
						}
						datas[k] = cellValue;
					}
				}
				datas[cellNum] = sheet.getSheetName();// 将sheet的名称作为最后一列

				if (j == 0) {
					headers = datas;
				} else {
					datasList.add(datas);
				}
				// datasList.add(datas);
				// }
			}
		}
		final DataSet dataSet = new DataSet(headers, null, datasList, cosSet);
		return dataSet;
	}

	public static void main(String[] args) {
		try {
			final DataSet dataSet = readExcelPOI("D:\\西南区域DSL终端档案表.xls", 0);
			System.out.println("================================");
			/*
			 * Set<String> datas = dataSet.getConStrctSet(); String[] datastr =
			 * new String[datas.size()]; datastr = datas.toArray(datastr); for
			 * (int i = 0; i < datastr.length; i++) {
			 * System.out.println(datastr[i]); }
			 */

			final List<String[]> datas = dataSet.getDatasList();
			for (int i = 0; i < datas.size(); i++) {
				final String[] values = datas.get(i);
				for (int j = 0; j < values.length; j++) {
					System.out.println(values[j]);
				}
				System.out.println("--------------------------");
			}
		} catch (final Exception e) {
			e.printStackTrace();
		}
	}
}
